CREATE PROCEDURE dbo.pa_AmparoLegalidad_Fecha

-- Consulta/Reporte Amparos de Legalidad
-- solicitada por Ana Lorena Brenes 2011-03-22

-- Parametros 

@Fecha_Ingreso_CHAR VARCHAR(10)	-- yyyy-mm-dd

AS 

DECLARE
@Fecha_Ingreso smalldatetime
SET @Fecha_Ingreso = CONVERT( smalldatetime , @Fecha_Ingreso_CHAR , 121 )


----------------------------------------
-- PASO 1 : Preselecciona Expedientes --
----------------------------------------
SELECT	Expediente , 
		Resolucion = SPACE(19) , 
		Partes = RTRIM( Promovente ) + ' CONTRA ' + RTRIM( Parte ) 
INTO	#Expediente
FROM	Expediente
WHERE	( YEAR( Fecha_Ing_PGR ) * 10000 ) + ( MONTH( Fecha_Ing_PGR ) * 100 ) + DAY( Fecha_Ing_PGR )
	= 
	( YEAR( @Fecha_Ingreso ) * 10000 ) + ( MONTH( @Fecha_Ingreso ) * 100 ) + DAY( @Fecha_Ingreso )

UPDATE	#Expediente SET Resolucion = CONVERT( varchar(19) , ( SELECT MIN( Fecha_PGR ) FROM Exp_Fecha AS n WHERE n.Expediente = e.Expediente ) , 121 )
FROM	#Expediente AS e

-- Recupera la primera Resolucion recibida --
UPDATE	#Expediente SET Resolucion = n.Resolucion 
FROM	#Expediente AS e , Exp_Fecha n
WHERE	e.Expediente = n.Expediente AND e.Resolucion = CONVERT( varchar(19) , n.Fecha_PGR , 121 )


------------------------------------------------------
-- PASO 2 : Preselecciona Procuradores_x_Expediente --
------------------------------------------------------
SELECT	Expediente , 
		Procurador , 
		Materia = MateriaTipoAsunto ,
		Institucion_Involucrada = SPACE(1000) , 
		Institucion = Delito
INTO	#Procuradores_x_Expediente
FROM	Procuradores_x_Expediente 
WHERE	Primario_chk = '1' AND MateriaTipoAsunto IN ( 11001 , 11301 )


--------------------------------------------------------------
-- PASO 3 : Mezcla #Expediente y #Procuradores_x_Expediente --
--------------------------------------------------------------
SELECT	e.Expediente ,  
		e.Resolucion ,
		x.Procurador , 
		e.Partes , 
		x.Materia ,
		x.Institucion_Involucrada , 
		x.Institucion
INTO	#Datos
FROM	#Expediente AS e
		INNER JOIN #Procuradores_x_Expediente AS x ON x.expediente = e.expediente


---------------------------------------------------------
-- PASO 4 : Preselecciona MateriaTipoAsunto de interes --
---------------------------------------------------------
SELECT	MateriaTipoAsunto , Nombre
INTO	#MateriaTipoAsunto
FROM	MateriaTipoAsunto
WHERE	MateriaTipoAsunto IN ( 11001 , 11301 )


---------------------------------------------
-- PASO 5 : Genera la tabla final de datos --
---------------------------------------------
SELECT	d.Expediente , 
		d.Resolucion , 
		Nombre_Procurador = LEFT( v.Nombre_Procurador , 35 ) , 
		d.Partes , 
		MateriaTipoAsunto = LEFT( m.nombre , 25 ) ,
		Institucion_Involucrada , 
		d.Institucion 
INTO	#Tabla
FROM	#Datos AS d
	INNER JOIN View_Nombre_Procuradores AS v ON v.procurador = d.procurador 
	INNER JOIN #MateriaTipoAsunto AS m ON m.materiatipoasunto = d.materia


------------------------------------------------------
-- PASO 6 : Recupera las Instituciones Involucradas --
------------------------------------------------------
UPDATE #Tabla SET Institucion_Involucrada = LTRIM( Institucion_Involucrada )

DECLARE Consulta CURSOR FOR
	SELECT 	Expediente ,
			InstitucionInvolucrada
	FROM 	Instituciones_x_Expediente 
	WHERE 	Expediente in ( SELECT Expediente FROM #Tabla ) AND InstitucionInvolucrada <> 0
OPEN Consulta

DECLARE @Expediente varchar(20) , @InstitucionInvolucrada int , @Institucion_Nombre varchar(100)

FETCH NEXT FROM Consulta INTO @Expediente , @InstitucionInvolucrada
WHILE (@@fetch_status <> -1)
	BEGIN
	SET @Institucion_Nombre  = RTRIM( ( SELECT i.Nombre 
										FROM InstitucionInvolucrada i 
										WHERE i.InstitucionInvolucrada = @InstitucionInvolucrada ) ) 
print @Expediente + ' ' + convert( varchar(5) , @InstitucionInvolucrada ) + ' ' + @Institucion_Nombre
	UPDATE #Tabla SET Institucion_Involucrada  = Institucion_Involucrada + ', ' + @Institucion_Nombre
		WHERE Expediente = @Expediente
	FETCH NEXT FROM Consulta INTO @Expediente , @InstitucionInvolucrada
	END
DEALLOCATE Consulta
UPDATE #Tabla SET Institucion_Involucrada = RIGHT( Institucion_Involucrada , LEN( Institucion_Involucrada ) - 2 )
	WHERE RTRIM( Institucion_Involucrada ) <> ''

SELECT Expediente , Resolucion , Nombre_Procurador , Partes , MateriaTipoAsunto , Institucion_Involucrada
FROM #Tabla
ORDER BY Nombre_Procurador , Expediente
